package band.wukong.practice.springboot.boot.da.jdbc;


import band.wukong.practice.springboot.boot.da.entity.Musican;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.*;
import java.util.List;

/**
 * @author wukong(wukonggg@139.com)
 */
@Repository
class MusicanRepositoryJdbcImpl {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Transactional(readOnly = true)
    Integer count() {
        final String sql = "select count(1) from MUSICAN";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Transactional(readOnly = true)
    List<Musican> list() {
        final String sql = "select * from MUSICAN";
        return jdbcTemplate.query(sql, new MusicanRowMapper());
    }

    @Transactional(readOnly = true)
    Musican find(Integer id) {
        final String sql = "select * from MUSICAN where id = ?";
        try {
            return jdbcTemplate.queryForObject(sql, new Object[]{id}, new MusicanRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }


    Musican insert(final Musican m) {
        final String sql = "insert into MUSICAN(NAME, BIRTH) values(?,?)";

        KeyHolder holder = new GeneratedKeyHolder();

        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, m.getName());
            ps.setDate(2, new Date(m.getBirth().getTime()));
            return ps;
        }, holder);

        int newMusicanId = holder.getKey().intValue();
        m.setId(newMusicanId);
        return m;
    }

    public void update(final Musican m) {
        final String sql = "update MUSICAN set NAME = ? where ID = ?";
        jdbcTemplate.update(sql, new Object[]{m.getName(), m.getId()});
    }

    public void delete(final Integer id) {
        final String sql = "delete from MUSICAN where ID = ?";
        jdbcTemplate.update(sql, new Object[]{id}, new int[]{java.sql.Types.INTEGER});
    }


    private class MusicanRowMapper implements RowMapper<Musican> {

        @Override
        public Musican mapRow(ResultSet rs, int rowNum) throws SQLException {
            Musican m = new Musican();
            m.setId(rs.getInt("id"));
            m.setName(rs.getString("name"));
            m.setBirth(rs.getDate("birth"));
            m.setAlive(rs.getBoolean("alive"));
            return m;
        }
    }
}
